package ch.zbw.pra.sysventory.domain.dbconnector;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import ch.zbw.pra.sysventory.domain.util.MyLogger;

public class MySQLConnector implements IDBConnector {
	private static final org.slf4j.Logger log = org.slf4j.LoggerFactory
			.getLogger(MySQLConnector.class);
	private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
	private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/sysVentory_V1";
	private static final String DB_USER = "root";
	private static final String DB_PASSWORD = "test";
	private static MySQLConnector instance;

	private MySQLConnector() {
	}

	public static MySQLConnector getInstance() {
		if (instance == null) {
			instance = new MySQLConnector();
		}
		return instance;
	}

	private Connection dbConnection;
	private PreparedStatement displayStatement;
	private ResultSet dataResultSet;
	private int updateCount;

	public ResultSet executeQuery(String query) {
		this.dataResultSet = null;
		try {
			MyLogger.logDebug(log, "Try Query to DB: " + query);
			this.displayStatement = this.dbConnection.prepareStatement(query);
			this.dataResultSet = this.displayStatement.executeQuery();
			return this.dataResultSet;
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		return this.dataResultSet;
	}

	public int updateQuery(String query) {
		this.updateCount = 0;
		try {
			MyLogger.logDebug(log, "Try Query to DB: " + query);
			this.displayStatement = this.dbConnection.prepareStatement(query);
			this.updateCount = this.displayStatement.executeUpdate();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		return this.updateCount;
	}

	public void connect(String driver, String dbURL, String user, String password) {
		try {
			MyLogger.logDebug(log, "...get Connection to MySQL-DB:   " + dbURL + "   User: " + user
					+ "  Password: " + password);
			Class.forName("com.mysql.jdbc.Driver");
			this.dbConnection = DriverManager.getConnection(dbURL, user, password);
			MyLogger.logDebug(log, "Connection to MySQL-DB established");
		} catch (Exception e) {
			MyLogger.logDebug(log, "MySQL-DB not connected!");
			MyLogger.logError(log, e.toString());
		}
	}

	public void connect() {
		try {
			MyLogger.logDebug(log, "...get Connection to MySQL-DB:   " + DB_CONNECTION
					+ "   User: " + DB_USER + "  Password: " + DB_PASSWORD);
			Class.forName(DB_DRIVER);
			this.dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
			MyLogger.logDebug(log, "Connection to MySQL-DB established");
		} catch (Exception e) {
			MyLogger.logDebug(log, "MySQL-DB not connected!");
			MyLogger.logError(log, e.toString());
		}
	}

	public boolean checkConnection() {
		boolean isClosed = false;
		try {
			isClosed = this.dbConnection.isClosed();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return isClosed;
	}

	public void closeConnection() {
		try {
			if (!this.dbConnection.isClosed()) {
				this.dbConnection.close();
				MyLogger.logDebug(log, "Connection to MySQL-DB closed.");
			}
		} catch (SQLException e) {
			if (log.isDebugEnabled()) {
				MyLogger.logDebug(log, "Cannot close Connection to MySQL-DB");
			}
			MyLogger.logError(log, e.toString());
		}
	}

	public int getLastInsertedId() {
		this.dataResultSet = null;
		int autoIncKeyFromFunc = -1;
		this.dataResultSet = executeQuery("SELECT LAST_INSERT_ID()");
		try {
			if (this.dataResultSet.next()) {
				autoIncKeyFromFunc = this.dataResultSet.getInt(1);
			} else {
				// throw an exception from here
			}
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
		return autoIncKeyFromFunc;
	}

	public void setAutocommit(boolean state) {
		try {
			this.dbConnection.setAutoCommit(state);
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
	}

	public void commit() {
		try {
			this.dbConnection.commit();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
	}

	public void rollback() {
		try {
			this.dbConnection.rollback();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
	}

	public void closeResultSetAndPreparedStatement() {
		try {
			if (!this.dataResultSet.isClosed()) {
				this.dataResultSet.close();
			}
			if (!this.displayStatement.isClosed()) {
				this.displayStatement.close();
			}
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
		}
	}

}
